#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into checkin_dwb.dwb_student_attend
select
-- fact_dwd_tbh_student_signin_record 学生打卡表
    ssr.id,
    ssr.normal_class_flag,
    ssr.time_table_id,
    ssr.class_id,
    ssr.student_id,
    ssr.signin_time,
    ssr.signin_date,
    ssr.inner_flag,
    ssr.signin_type,
    ssr.share_state,
    ssr.inner_ip,
-- dim_dwd_course_table_upload_detail 课程排期表,
    ctud.base_id,
    ctud.class_date,
    ctud.content,
    ctud.teacher_id,
    ctud.teacher_name,
    ctud.job_number,
    ctud.classroom_id,
    ctud.classroom_name,
    ctud.is_outline,
    ctud.class_mode,
    ctud.is_stage_exam,
    ctud.is_pay,
-- dim_dwd_tbh_class_time_table 班级作息表,
    tctt.morning_template_id,
    tctt.morning_begin_time,
    tctt.morning_end_time,
    tctt.afternoon_template_id,
    tctt.afternoon_begin_time,
    tctt.afternoon_end_time,
    tctt.evening_template_id,
    tctt.evening_begin_time,
    tctt.evening_end_time,
    tctt.use_begin_date,
    tctt.use_end_date,
    tctt.create_time,
    tctt.create_person,
    '2023-05-05' as dt
from (select * from checkin_dwd.dim_dwd_course_table_upload_detail where content is not null and content != '开班典礼') ctud
join checkin_dwd.fact_dwd_tbh_student_signin_record ssr on ssr.class_id = ctud.class_id and ssr.signin_date = ctud.class_date and ssr.share_state = 1
join checkin_dwd.dim_dwd_tbh_class_time_table tctt on ssr.class_id = tctt.class_id and ctud.class_date >= tctt.use_begin_date and ctud.class_date <= tctt.use_end_date;

select * from  checkin_dwb.dwb_student_attend;

insert into checkin_dwb.dwb_student_leave
select
-- fact_dwd_student_leave_apply 请假表
sla.  id,
sla.class_id,
sla.student_id,
sla.audit_state,
sla.audit_person,
sla.audit_time,
sla.audit_remark,
sla.leave_type,
sla.leave_reason,
sla.begin_time,
sla.begin_time_type,
sla.end_time,
sla.end_time_type,
sla.days,
sla.cancel_state,
sla.cancel_time,
sla.old_leave_id,
sla.leave_remark,
sla.valid_state,
-- dim_dwd_course_table_upload_detail 课程排期表,
    ctud.base_id,
    ctud.class_date,
    ctud.content,
    ctud.teacher_id,
    ctud.teacher_name,
    ctud.job_number,
    ctud.classroom_id,
    ctud.classroom_name,
    ctud.is_outline,
    ctud.class_mode,
    ctud.is_stage_exam,
    ctud.is_pay,
-- dim_dwd_tbh_class_time_table 班级作息表,
    tctt.morning_template_id,
    tctt.morning_begin_time,
    tctt.morning_end_time,
    tctt.afternoon_template_id,
    tctt.afternoon_begin_time,
    tctt.afternoon_end_time,
    tctt.evening_template_id,
    tctt.evening_begin_time,
    tctt.evening_end_time,
    tctt.use_begin_date,
    tctt.use_end_date,
    tctt.create_time,
    tctt.create_person,
-- dim_dwd_class_studying_student_count 在读学生表
    cssc.school_id,
    cssc.subject_id,
    cssc.studying_student_count,
    cssc.studying_date,
    '2023-05-05' as dt
from (select * from checkin_dwd.dim_dwd_course_table_upload_detail where content is not null and content != '开班典礼') ctud
join checkin_dwd.fact_dwd_student_leave_apply sla on sla.class_id = ctud.class_id and sla.audit_state = 1 and sla.cancel_state = 0 and sla.valid_state = 1
join checkin_dwd.dim_dwd_tbh_class_time_table tctt on sla.class_id = tctt.class_id and ctud.class_date between tctt.use_begin_date and tctt.use_end_date
join checkin_dwd.dim_dwd_class_studying_student_count cssc on ctud.class_id = cssc.class_id and cssc.studying_date = ctud.class_date
 where sla.class_id is not null;
"
